package fr.esgi.donnees;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import fr.esgi.classes.Groupe;
import fr.esgi.classes.Livrable;
import fr.esgi.classes.Personne;
import fr.esgi.classes.Projet;
import fr.esgi.classes.Promotion;
import fr.esgi.classes.Soutenance;

public class Database {
	String motDePasse = "esgi";
	String db = "esgiprojet_esgi";
	//en local
	//	String url = "jdbc:mysql://localhost:3306/";
	//distant
	String url = "jdbc:mysql://mysql2.alwaysdata.com/";
	//en local
	//String utilisateur = "esgi";
	//distant
	String utilisateur = "74074_root";
	String driver = "com.mysql.jdbc.Driver";    
	Connection con = null;
	public boolean verifEmail(String mail) throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM personne where email='"+mail+"');";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		if(rs.first()){
			return false;
		}
		else {
			return true;
		}
	}	

	// insert un nouvel ��tudiant dans la base 
	public void insertEtudiant(Personne e) throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="INSERT INTO personne VALUES(null,'"+e.getNom()+"','"+e.getPrenom()+"','"+e.getPromotion()+"',"+e.getGroupe()+",0,'etudiant','"+e.getEmail()+"',md5('"+e.getMdp()+"'));";
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
	}

	// insert une personne de l'administration ou un professeur
	public void insertAdministration(Personne e) throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="INSERT INTO personne VALUES(null,'"+e.getNom()+"','"+e.getPrenom()+"',null,null,null,'"+e.getRole()+"','"+e.getEmail()+"',md5('"+e.getMdp()+"'));";
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
	}


	//fonction qui v��rifie qu'une personne est bien pr��sente dans la base de donn��es, et que l'utilisateur peut se connecter
	public boolean verificationPersonne(String mail,String password) throws SQLException, ClassNotFoundException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM personne where email='"+mail+"' and password=md5('"+password+"');";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		if(rs.first()){
			return true;
		}
		else {
			return false;
		}
	}
	//fonction qui tous les attributs d'une personne
	public Personne buildPersonne(String mail) throws SQLException, ClassNotFoundException{
		Personne p = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM personne where email='"+mail+"';";
		ResultSet rs = state.executeQuery(requete);
		while(rs.next()){
			int uid=rs.getInt(1);
			String nom=rs.getString(2);
			String prenom=rs.getString(3);
			String promotion=rs.getString(4);
			int groupe=rs.getInt(5);
			String role=rs.getString(7);
			String email=rs.getString(8);
			p=new Personne(uid,nom, prenom, promotion, groupe, email, role);
		}
		return p;
	}

	//affiche les sp��cialit��s pour les promotions 
	public ArrayList<String>afficherPromo() throws SQLException, ClassNotFoundException{
		ArrayList<String> listPromo=new ArrayList<String>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();//and date_Rendu>Now();
		ResultSet rs = state.executeQuery("select distinct anneeSpecialite from promotion order by anneeSpecialite ;");
		while(rs.next())
		{
			String s=rs.getString(1);
			listPromo.add(s);
		}
		return listPromo;
	}
	//affiche les promotions 
	public ArrayList<Promotion>afficherPromotion() throws SQLException, ClassNotFoundException{
		ArrayList<Promotion> tabPromotion=new ArrayList<Promotion>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT distinct * FROM promotion order by anneeSpecialite,groupe ;");
		while(rs.next())
		{
			int id = rs.getInt(1);
			String promotion = rs.getString(2);
			int groupe = rs.getInt(3);
			Promotion p = new Promotion(id,promotion,groupe);
			tabPromotion.add(p);
		}
		return tabPromotion;
	}		

	public boolean UniqueEmail(String mail) throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM personne where email='"+mail+"';";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		if(rs.first()){
			return false;
		}
		else {
			return true;
		}
	}	
	public boolean UniqueProjet(String nom) throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM projet where nom='"+nom+"';";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		if(rs.first()){
			return false;
		}
		else {
			return true;
		}
	}	






	//affiche les professeurs 
	public ArrayList<Personne>afficherProfesseur() throws SQLException, ClassNotFoundException{
		ArrayList<Personne> tabProfesseur=new ArrayList<Personne>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT  * FROM personne where role='professeur';");
		while(rs.next())
		{
			int id = rs.getInt(1);
			String nom = rs.getString(2);
			String prenom = rs.getString(3);
			String email = rs.getString(8);
			Personne p = new Personne(id,nom,prenom,email);
			tabProfesseur.add(p);
		}
		return tabProfesseur;
	}
	public ArrayList <Projet> afficherProjet() throws SQLException, ClassNotFoundException{
		ArrayList <Projet> projets=new ArrayList<Projet>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT *  FROM projet;");
		while(rs.next())
		{
			int id = rs.getInt(1);
			String nom = rs.getString(2);
			String enseignant=rs.getString(5);
			String promotion=rs.getString(6);
			int nbEtudiants=rs.getInt(7);
			int groupe=rs.getInt(8);
			Projet p=new Projet(id,nom,enseignant,promotion,nbEtudiants,groupe);
			projets.add(p);
		}
		return projets;
	}
	public Projet buildProjet(String uid) throws SQLException, ClassNotFoundException{
		Projet p = null;
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT *  FROM projet where id="+uid+";");
		while(rs.next())
		{
			int id = rs.getInt(1);
			String nom = rs.getString(2);
			String enseignant=rs.getString(5);
			String promotion=rs.getString(6);
			int nbEtudiants=rs.getInt(7);
			int groupe=rs.getInt(8);
			p=new Projet(id,nom,enseignant,promotion,nbEtudiants,groupe);

		}
		return p;
	}

	public Projet buildProject(String nom) throws SQLException, ClassNotFoundException{
		Projet p = null;
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT *  FROM projet where nom='"+nom+"';");
		while(rs.next())
		{
			int id = rs.getInt(1);
			String enseignant=rs.getString(5);
			String promotion=rs.getString(6);
			int nbEtudiants=rs.getInt(7);
			int groupe=rs.getInt(8);
			p=new Projet(id,nom,enseignant,promotion,nbEtudiants,groupe);

		}
		return p;
	}

	public void insertLivrable(Livrable l) throws ClassNotFoundException, SQLException{
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		String requete="INSERT into Livrable (id_projet_PK,id_livrable_PK,nom,regle,date_rendu)" +
				"VALUES('"+l.getIdProjet()+"','"+l.getIdLivrable()+"','"+l.getNom()+"','"+l.getDescription()+"','"+l.getDateRendu()+"');";
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
	}

	public ArrayList GetGroupeByIDprojectAndIDGroup(String idProject,String IdGrp) throws SQLException, ClassNotFoundException{
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String Tratement="Requete::GetGroupeByIDprjectAndIDGroup ";
		ArrayList<Personne> tabEtudiant=new ArrayList<Personne>();
		ArrayList Groupe=new ArrayList();
		System.out.println(Tratement+" START");
		String req = "SELECT * FROM equipe where id_projet='"+idProject+"' AND id_groupe='"+IdGrp+"'";
		System.out.println(req); 
		ResultSet rs = state.executeQuery(req);
		while(rs.next())
		{
			int idProjet = rs.getInt(1);
			int idGroupe = rs.getInt(2);
			String etudiantId1 = rs.getString(3);
			String etudiantId2 = rs.getString(4);
			String etudiantId3 = rs.getString(5);
			String etudiantId4 = rs.getString(6);
			String etudiantId5 = rs.getString(7);
			String etudiantId6 = rs.getString(8);
			String etudiantId7 = rs.getString(9);
			String etudiantId8 = rs.getString(10);
			String etudiantId9 = rs.getString(11);
			String etudiantId10 = rs.getString(12);

			Groupe.add(idProjet);
			Groupe.add(idGroupe);
			Groupe.add(etudiantId1);
			Groupe.add(etudiantId2);
			Groupe.add(etudiantId3);
			Groupe.add(etudiantId4);
			Groupe.add(etudiantId5);
			Groupe.add(etudiantId6);
			Groupe.add(etudiantId7);
			Groupe.add(etudiantId8);
			Groupe.add(etudiantId9);
			Groupe.add(etudiantId10);


		} 

		for(int i=0;i<12;i++)
		{

			System.out.println("table groupe :"+Groupe.get(i));
		}
		System.out.println(Tratement+" END");	
		return Groupe;



	}


	public Personne GetEtudiantByID(String uid) throws ClassNotFoundException, SQLException
	{
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String Tratement="Requete::GetStudent ";

		Personne student = new Personne();
		ArrayList<Personne> tabEtudiant=new ArrayList<Personne>();

		System.out.println(Tratement+" START");
		ResultSet rs = state.executeQuery("SELECT * FROM etudiant where uid='"+uid+"'");
		while(rs.next())
		{

			int UID = rs.getInt(6);
			String NomEtudiant = rs.getString(1);
			String PrenomEtudiant = rs.getString(2);
			String Promotion= rs.getString(3);
			int groupe= rs.getInt(4);
			String valdProjet=rs.getString(5);

			System.out.println(NomEtudiant);
			student.setUid(UID);
			student.setNom(NomEtudiant);
			student.setPrenom(PrenomEtudiant);
			student.setGroupe(groupe);
			student.setPromotion(Promotion);

		}
		//grp.setNombreEtudiant(tabEtudiant.size());
		//grp.setEtudiants(tabEtudiant);

		System.out.println(Tratement+" END");	
		return  student;
	}
	public ArrayList<Personne> ListEtudiantGroupeByIDgroupeAndIDProj(String IDprojet,String IDgroupe) throws ClassNotFoundException, SQLException
	{
		String Tratement="Database::ListEtudiantGroupeByIDgroupeAndIDProj ";
		System.out.println(Tratement+": START");

		System.out.println("ID projet: "+IDprojet);
		System.out.println("ID groupe: "+IDgroupe);
		ArrayList<Personne> ListEtudiant=new ArrayList();
		ArrayList groupe=GetGroupeByIDprojectAndIDGroup(IDprojet, IDgroupe);
		System.out.println("taille de la liste :"+groupe.size());
		for(int i=2;i<groupe.size();i++)
		{

			ListEtudiant.add(GetEtudiantByID(groupe.get(i).toString()));
			System.out.println("ListEtudiant.get(i) :"+ListEtudiant.get(i-2).getNom());
		}

		System.out.println(Tratement+": END");
		return ListEtudiant;

	}

	//insert un projet dans la base de données
	public void insertProjet(Projet p) throws ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		String requete ="INSERT INTO projet VALUES(null,'"+p.getNom()+"','"+p.getDescription()+"','"+p.getCompositionGroupes()+"','"+p.getEnseignant()+"','"+p.getPromotion()+"',"+p.getNbEtudiantRequis()+","+p.getGroupe()+");";
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
	}

	public ArrayList<Personne> recupEtudiant(String promotion,String groupe) throws SQLException, ClassNotFoundException{
		ArrayList	<Personne>etudiants=new ArrayList <Personne>();
		Personne p = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM personne where promotion='"+promotion+"' and groupe='"+groupe+"';";
		ResultSet rs = state.executeQuery(requete);
		while(rs.next()){
			int uid=rs.getInt(1);
			String nom=rs.getString(2);
			String prenom=rs.getString(3);
			String promo=rs.getString(4);
			int gr=rs.getInt(5);
			String role=rs.getString(7);
			String email=rs.getString(8);
			p=new Personne(uid, nom, prenom, promo,gr, email, role)	;		
			etudiants.add(p);
		}
		return etudiants;
	}
	// insert un nouvel ��tudiant dans la base 
	public void insertEquipe(ArrayList<Groupe> groupes,String idProjet) throws ClassNotFoundException, SQLException{
		int idEquipe = 0;
		String s="";
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		for(int i =0;i<groupes.size();i++){
			idEquipe=	groupes.get(i).getId();
			s="";

			for (int j = 0; j < groupes.get(i).getTabEtudiant().size(); j++) {
				if(j==9){
					s=s+groupes.get(i).getTabEtudiant().get(j).getUid();
				}else 
					s=s+groupes.get(i).getTabEtudiant().get(j).getUid()+",";
			}
			if(groupes.get(i).getTabEtudiant().size()!=10)
				for (int j =groupes.get(i).getTabEtudiant().size(); j <10; j++) {
					if(j==9){
						s=s+"null";
					}else 
						s=s+"null,";

				}
			String requete ="INSERT INTO equipe VALUES("+idProjet+","+idEquipe+","+s+");";
			System.out.println(requete);
			Statement st = con.createStatement();
			st.executeUpdate(requete);	
		}
	}

	public void deleteEquipes(String projet) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		String requete ="DELETE from equipe where id_projet="+projet;
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);

	}
	public void deleteLivrable(String projet) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		String requete ="DELETE from Livrable where id_projet_PK="+projet;
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);

	}

	public void deleteSoutenance(String projet) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		String requete ="DELETE from soutenance where id_projet="+projet;
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
	}
	public void insertSoutenance(String projet,String equipe, String horaire,String date) throws ClassNotFoundException, SQLException{
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		String requete="INSERT into soutenance (id_projet,id_equipe,horaire,date)" +
				"VALUES("+projet+","+equipe+",'"+horaire+"','"+date+"');";
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
		System.out.println("1 row affected");	
	}

	//insere une nouvelle promotion dans la base
	public void insertPromotion(Promotion p) throws ClassNotFoundException, SQLException{
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		String requete="INSERT into promotion (anneeSpecialite,groupe)" +
				"VALUES('"+p.getAnneeSpecialite()+"',"+p.getGroupe()+");";
		System.out.println(requete);
		Statement st = con.createStatement();
		st.executeUpdate(requete);
		System.out.println("1 row affected");	
	}
	public int verifPromotion(Promotion p) throws ClassNotFoundException, SQLException{
		int nb=0;
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String requete="SELECT count(*) FROM promotion where anneeSpecialite='"+p.getAnneeSpecialite()+"' and  groupe="+p.getGroupe()+";";
		ResultSet rs = state.executeQuery(requete);
		if(rs.first()){
			nb=rs.getInt(1);
		}
		return nb;
	}
	public ArrayList<Projet> afficherProjetEtudiant(String uid) throws ClassNotFoundException, SQLException{
		ArrayList<Projet> tabProjet=new ArrayList<Projet>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String requete="SELECT * FROM projet p,equipe g where g.id_projet=p.id and (etudiant1='"+uid
				+"' or etudiant2='"+uid+"' or etudiant3='"+uid+"' or etudiant4='"+uid+"' or etudiant5='"+uid+"' or etudiant6='"+uid+
				"' or etudiant7='"+uid+"' or etudiant8='"+uid+"' or etudiant9='"+uid+"' or etudiant10='"+uid+"');";
		ResultSet rs = state.executeQuery(requete);
		while(rs.next())
		{
			int id = rs.getInt(1);
			String nom = rs.getString(2);
			String regle = rs.getString(3);
			String constitution = rs.getString(4);
			String enseignant = rs.getString(5);
			String promotion = rs.getString(6);
			int nb = rs.getInt(7);
			int groupe = rs.getInt(8);
			Projet projet = new Projet(id,nom,regle,enseignant,nb,promotion,groupe,constitution);
			tabProjet.add(projet);
			System.out.println(projet.getId());

		}
		return tabProjet;
	}
	public ArrayList<Livrable> afficherLivrable() throws ClassNotFoundException, SQLException{
		ArrayList<Livrable> tabLivrable=new ArrayList<Livrable>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();//and date_Rendu>Now();
		ResultSet rs = state.executeQuery("SELECT * FROM Livrable");
		while(rs.next())
		{
			int idProjet = rs.getInt(1);
			int idLivrable = rs.getInt(2);
			String nom = rs.getString(3);
			String description = rs.getString(4);
			String date=rs.getString(5);
			Livrable livrable = new Livrable(nom,description,date, idProjet,idLivrable);
			tabLivrable.add(livrable);
			System.out.println("id ="+livrable.getIdProjet());
		}
		return tabLivrable;
	}
	public ArrayList<Soutenance> afficherSoutenance(String idProjet) throws ClassNotFoundException, SQLException{
		ArrayList<Soutenance> tab=new ArrayList<Soutenance>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT * FROM soutenance where id_projet="+idProjet+";");
		while(rs.next())
		{
			Soutenance s = new Soutenance(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4));
			tab.add(s);
		}
		return tab;
	}
	
	//supprime un projet
	public void deleteProjet(String id) throws ClassNotFoundException, SQLException{
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		String requete="delete from projet where id='"+id+"'";
		Statement st = con.createStatement();
		int delete= st.executeUpdate(requete);
		if (delete==1)System.out.println("delete");
		else System.out.println("ligne pas delete");

	}
	public ArrayList<Groupe> afficherEquipe(String id) throws ClassNotFoundException, SQLException{
		ArrayList<Groupe> groupe=new ArrayList<Groupe>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("SELECT * FROM equipe where id_projet="+id+";");
		while(rs.next())
		{
			Groupe g=new Groupe(rs.getInt(2));

			g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(3))));
			if(rs.getInt(4)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(4))));
			}
			if(rs.getInt(5)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(5))));
			}
			if(rs.getInt(6)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(6))));
			}
			if(rs.getInt(7)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(7))));
			}
			if(rs.getInt(8)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(8))));
			}
			if(rs.getInt(9)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(9))));
			}
			if(rs.getInt(10)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(10))));
			}
			if(rs.getInt(11)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(11))));
			}
			if(rs.getInt(12)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(12))));
			}
			groupe.add(g);
		}
		return groupe;
	}
	public Personne getStudentById(String uid) throws SQLException, ClassNotFoundException{
		Personne p = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = con.createStatement();
		String requete ="SELECT * FROM personne where uid='"+uid+"';";
		ResultSet rs = state.executeQuery(requete);
		while(rs.next()){
			int id=rs.getInt(1);
			String nom=rs.getString(2);
			String prenom=rs.getString(3);
			String promotion=rs.getString(4);
			int groupe=rs.getInt(5);
			String role=rs.getString(7);
			String email=rs.getString(8);
			p=new Personne(id,nom, prenom, promotion, groupe, email, role);
		}
		return p;
	}
	public ArrayList<Livrable> recuperationLivrable(String projet) throws ClassNotFoundException, SQLException{
		ArrayList<Livrable> livrables=new ArrayList<Livrable>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String requete="SELECT * FROM Livrable where id_projet_PK="+projet+";";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		while(rs.next())
		{

			int idProjet = rs.getInt(1);
			int idLivrable=rs.getInt(2);
			String nom=rs.getString(3);
			String description=rs.getString(4);
			String date=rs.getString(5);
			Livrable l= new Livrable(nom,description, date,idProjet, idLivrable);
			livrables.add(l);
		}
		return livrables;
	}

	public int recuperationGroupe(String uid,String projet) throws ClassNotFoundException, SQLException{
		int idgroupe = 0;
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String requete="SELECT id_equipe FROM equipe g where id_projet="+projet+" and (etudiant1='"+uid
				+"' or etudiant2='"+uid+"' or etudiant3='"+uid+"' or etudiant4='"+uid+"' or etudiant5='"+uid+"' or etudiant6='"+uid+
				"' or etudiant7='"+uid+"' or etudiant8='"+uid+"' or etudiant9='"+uid+"' or etudiant10='"+uid+"');";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		while(rs.next())
		{
			idgroupe = rs.getInt(1);

		}
		return idgroupe;
	}
	
	public ArrayList<Personne> recuperationEtudiant(String projet ,String equipe) throws ClassNotFoundException, SQLException{
		ArrayList<Personne>personnes =new ArrayList <Personne>();
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url+db, utilisateur, motDePasse);
		Statement state = conn.createStatement();
		String requete="SELECT etudiant1, etudiant2, etudiant3, etudiant4, etudiant5, etudiant6, etudiant7, etudiant8, etudiant9, etudiant10 FROM  equipe e WHERE id_equipe="+equipe+" and id_projet="+projet+";";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		while(rs.next())
		{
			if(rs.getInt(1)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(2))));
			}
			if(rs.getInt(2)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(2))));
			}
			if(rs.getInt(3)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(3))));
			}
			if(rs.getInt(4)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(4))));
			}
			if(rs.getInt(5)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(5))));
			}
			if(rs.getInt(6)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(6))));
			}
			if(rs.getInt(7)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(7))));
			}
			if(rs.getInt(8)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(9))));
			}
			if(rs.getInt(9)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(9))));
			}
			if(rs.getInt(10)!=0){
				personnes.add(getStudentById(String.valueOf(rs.getInt(10))));
			}
		}
		return personnes;
	}
	public void insertOrUpdateRendu(String projet,String livrable,String groupe,String date) throws SQLException, ClassNotFoundException {
		// TODO Auto-generated method stub
		System.out.println("je passe par la ");
		Connection con = null;
		int count = 0;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		Statement state = con.createStatement();
		String requete="SELECT count(*) FROM rendu where id_projet='"+projet+"' and id_livrable='"+livrable+"' and id_equipe='"+groupe+"';";
		System.out.println(requete);
		ResultSet rs = state.executeQuery(requete);
		if(rs.first()){
			count=rs.getInt(1);
		}
		if(count==0){
			requete="INSERT into rendu(id_projet,id_livrable,id_equipe,dateRendu)" +
					"VALUES('"+projet+"','"+livrable+"','"+groupe+"','"+date+"');";
			System.out.println(requete);
			state.executeUpdate(requete);
		}
		else{
			requete="Update rendu set dateRendu='"+date+"' where id_projet="+projet+" and  id_livrable="+livrable+" " +
					"and  id_equipe="+groupe+";";
			System.out.println(requete);		
			state.executeUpdate(requete);
		}
	}

	public ArrayList<Groupe> retardGroupe(String idProjet) throws SQLException, ClassNotFoundException {
		// TODO Auto-generated method stub
		ArrayList<Groupe> groupe=new ArrayList<Groupe>();
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		String requete="SELECT r.id_livrable, e.id_equipe, etudiant1, etudiant2, etudiant3, etudiant4, etudiant5, etudiant6, etudiant7, etudiant8, etudiant9, etudiant10, DATEDIFF( STR_TO_DATE( r.dateRendu,  '%d/%m/%Y' ) , STR_TO_DATE( l.date_rendu,  '%d/%m/%Y' ) ) FROM Livrable l, equipe e, rendu r WHERE e.id_projet = l.id_projet_PK AND r.id_projet = e.id_projet	AND l.id_projet_PK ="+idProjet+" AND DATEDIFF( STR_TO_DATE( r.dateRendu,  '%d/%m/%Y' ) , STR_TO_DATE( l.date_rendu,  '%d/%m/%Y' ) ) >0 GROUP BY l.id_projet_PK, r.id_equipe, l.id_livrable_PK";	
		System.out.println(requete);
		Statement state = con.createStatement();
		ResultSet rs = state.executeQuery(requete);
		while(rs.next())
		{
			Groupe g=new Groupe(rs.getInt(2));
			g.setLivrableRetard(rs.getInt(1));
			g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(3))));
			if(rs.getInt(4)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(4))));
			}
			if(rs.getInt(5)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(5))));
			}
			if(rs.getInt(6)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(6))));
			}
			if(rs.getInt(7)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(7))));
			}
			if(rs.getInt(8)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(8))));
			}
			if(rs.getInt(9)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(9))));
			}
			if(rs.getInt(10)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(10))));
			}
			if(rs.getInt(11)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(11))));
			}
			if(rs.getInt(12)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(12))));
			}

			g.setLivrableRendu(rs.getInt(13)+"jours de retard");

			groupe.add(g);
		}
		return groupe;

	}
	public ArrayList<Groupe> ProjetNonRendu(String idProjet) throws SQLException, ClassNotFoundException {
		// TODO Auto-generated method stub
		ArrayList<Groupe> groupe=new ArrayList<Groupe>();
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url+db,utilisateur,motDePasse);
		String requete="SELECT l.id_livrable_PK, e.id_equipe, etudiant1, etudiant2, etudiant3, etudiant4, etudiant5, etudiant6, etudiant7, etudiant8, etudiant9, etudiant10 FROM Livrable l, equipe e WHERE e.id_projet = l.id_projet_PK and e.id_equipe not in (select id_equipe from rendu r, Livrable l where l.id_projet_PK=r.id_projet and r.id_projet="+idProjet+")  and e.id_projet="+idProjet+";";	
		System.out.println(requete);
		Statement state = con.createStatement();
		ResultSet rs = state.executeQuery(requete);
		while(rs.next())
		{
			Groupe g=new Groupe(rs.getInt(2));
			g.setLivrableRetard(rs.getInt(1));
			g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(3))));
			if(rs.getInt(4)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(4))));
			}
			if(rs.getInt(5)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(5))));
			}
			if(rs.getInt(6)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(6))));
			}
			if(rs.getInt(7)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(7))));
			}
			if(rs.getInt(8)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(8))));
			}
			if(rs.getInt(9)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(9))));
			}
			if(rs.getInt(10)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(10))));
			}
			if(rs.getInt(11)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(11))));
			}
			if(rs.getInt(12)!=0){
				g.getTabEtudiant().add(getStudentById(String.valueOf(rs.getInt(12))));
			}	
			groupe.add(g);
		}
		return groupe;

	}
}


